﻿using Electronic_work_order.Model;
using Microsoft.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;

namespace Electronic_work_order.Interfacess.OA_Agency
{
    public class SQL_Agency : Interfacess_Agency
    {
        private readonly AppDbContext _context;

        public SQL_Agency(AppDbContext context)
        {
            _context = context;
        }

        public int AddCommission(commission commission)
        {
            _context.commissions.Add(commission);
            return _context.SaveChanges();
        }


        //根据流水号，申请流程名称，申请状态查询
        public IEnumerable<object> GetCcommission(string Agency_ID, int Aid, int StateID)
        {
            var Agencys = (from commission in _context.commissions
                           join Organizations in _context.organizations on commission.OrganizationId equals Organizations.OrganizationID
                           join application in _context.applications on commission.Aid equals application.Aid
                           join state in _context.Process_application_State on commission.Process_State_ID equals state.Process_State_ID
                           join Position in _context.Position on commission.Manager_id equals Position.PositionID
                           join Users in _context.Users on Position.PositionID equals Users.PositionID
                           where commission.Agency_ID.Contains(Agency_ID) && commission.Aid == Aid && commission.Process_State_ID == StateID
                           select new
                           {
                               commission.Agency_ID,
                               commission.ApplicationName,
                               Organizations.OrganizationName,
                               application.Aname,
                               commission.Manager_id,
                               commission.Director_Id,
                               state.Process_State_Name,
                               commission.CreateTime,
                               state.Process_State_ID,
                               Users.UsersName,
                               Users.Nickname
                           }).ToList();
            return Agencys;
        }

        //申请流程名称，申请状态查询
        public IEnumerable<object> GetCcommission_AllID( int Aid, int StateID)
        {
            var Agencys = (from commission in _context.commissions
                           join Organizations in _context.organizations on commission.OrganizationId equals Organizations.OrganizationID
                           join application in _context.applications on commission.Aid equals application.Aid
                           join state in _context.Process_application_State on commission.Process_State_ID equals state.Process_State_ID
                           join Position in _context.Position on commission.Manager_id equals Position.PositionID
                           join Users in _context.Users on Position.PositionID equals Users.PositionID
                           where commission.Aid == Aid && commission.Process_State_ID == StateID
                           select new
                           {
                               commission.Agency_ID,
                               commission.ApplicationName,
                               Organizations.OrganizationName,
                               application.Aname,
                               commission.Manager_id,
                               commission.Director_Id,
                               state.Process_State_Name,
                               commission.CreateTime,
                               state.Process_State_ID,
                               Users.UsersName,
                               Users.Nickname
                           }).ToList();
            return Agencys;
        }

        //申请状态查询
        public IEnumerable<object> GetCcommission_StateID( int StateID)
        {
            var Agencys = (from commission in _context.commissions
                           join Organizations in _context.organizations on commission.OrganizationId equals Organizations.OrganizationID
                           join application in _context.applications on commission.Aid equals application.Aid
                           join state in _context.Process_application_State on commission.Process_State_ID equals state.Process_State_ID
                           join Position in _context.Position on commission.Manager_id equals Position.PositionID
                           join Users in _context.Users on Position.PositionID equals Users.PositionID
                           where commission.Process_State_ID == StateID
                           select new
                           {
                               commission.Agency_ID,
                               commission.ApplicationName,
                               Organizations.OrganizationName,
                               application.Aname,
                               commission.Manager_id,
                               commission.Director_Id,
                               state.Process_State_Name,
                               commission.CreateTime,
                               state.Process_State_ID,
                               Users.UsersName,
                               Users.Nickname
                           }).ToList();
            return Agencys;
        }

        //申请流程名称
        public IEnumerable<object> GetCcommission_Aid(int Aid)
        {
            var Agencys = (from commission in _context.commissions
                           join Organizations in _context.organizations on commission.OrganizationId equals Organizations.OrganizationID
                           join application in _context.applications on commission.Aid equals application.Aid
                           join state in _context.Process_application_State on commission.Process_State_ID equals state.Process_State_ID
                           join Position in _context.Position on commission.Manager_id equals Position.PositionID
                           join Users in _context.Users on Position.PositionID equals Users.PositionID
                           where commission.Aid == Aid 
                           select new
                           {
                               commission.Agency_ID,
                               commission.ApplicationName,
                               Organizations.OrganizationName,
                               application.Aname,
                               commission.Manager_id,
                               commission.Director_Id,
                               state.Process_State_Name,
                               commission.CreateTime,
                               state.Process_State_ID,
                               Users.UsersName,
                               Users.Nickname
                           }).ToList();
            return Agencys;
        }

        //根据流水号
        public IEnumerable<object> GetCcommission_Agency_ID(string Agency_ID)
        {
            var Agencys = (from commission in _context.commissions
                           join Organizations in _context.organizations on commission.OrganizationId equals Organizations.OrganizationID
                           join application in _context.applications on commission.Aid equals application.Aid
                           join state in _context.Process_application_State on commission.Process_State_ID equals state.Process_State_ID
                           join Position in _context.Position on commission.Manager_id equals Position.PositionID
                           join Users in _context.Users on Position.PositionID equals Users.PositionID
                           where commission.Agency_ID.Contains(Agency_ID)
                           select new
                           {
                               commission.Agency_ID,
                               commission.ApplicationName,
                               Organizations.OrganizationName,
                               application.Aname,
                               commission.Manager_id,
                               commission.Director_Id,
                               state.Process_State_Name,
                               commission.CreateTime,
                               state.Process_State_ID,
                               Users.UsersName,
                               Users.Nickname
                           }).ToList();
            return Agencys;
        }

        //查询主管级别的数据
        public IEnumerable<object> Agency(int State,int PositoionID)
        {

            var Agencys = (from commission in _context.commissions
                          join Organizations in _context.organizations on commission.OrganizationId equals Organizations.OrganizationID
                          join application in _context.applications on commission.Aid equals application.Aid
                          join state in _context.Process_application_State on commission.Process_State_ID equals state.Process_State_ID
                           join Position in _context.Position on commission.Manager_id equals Position.PositionID
                           join Users in _context.Users on Position.PositionID equals Users.PositionID  
                           where state.Process_State_ID==State && commission.Director_Id==PositoionID
                           select new
                          {
                              commission.Agency_ID,commission.ApplicationName,Organizations.OrganizationName,
                               commission.Aid,
                               application.Aname,commission.Manager_id,commission.Director_Id,state.Process_State_Name,
                              commission.CreateTime,state.Process_State_ID,
                               Users.UsersName,
                               Users.Nickname
                           }).ToList();
            return Agencys;
        }

        //查询经理级别的数据
        public IEnumerable<object> Agency_manager(int State, int PositoionID) {
            var Agencys = (from commission in _context.commissions
                           join Organizations in _context.organizations on commission.OrganizationId equals Organizations.OrganizationID
                           join application in _context.applications on commission.Aid equals application.Aid
                           join state in _context.Process_application_State on commission.Process_State_ID equals state.Process_State_ID
                           join Position in _context.Position on commission.Manager_id equals Position.PositionID
                           join Users in _context.Users on Position.PositionID equals Users.PositionID
                           where state.Process_State_ID == State && commission.Manager_id == PositoionID
                           select new
                           {
                               commission.Agency_ID,
                               commission.Aid,
                               commission.ApplicationName,
                               Organizations.OrganizationName,
                               application.Aname,
                               commission.Manager_id,
                               commission.Director_Id,
                               state.Process_State_Name,
                               commission.CreateTime,
                               state.Process_State_ID,
                               Users.UsersName,
                               Users.Nickname
                           }).ToList();
            return Agencys;
        }
        //根据职位查名称
        public object GetPosition_UserName(int PositionID)
        {
            var UserName = (from Position in _context.Position
                           join Users in _context.Users
                            on Position.PositionID equals Users.PositionID
                            where Position.PositionID==PositionID
                           select new
                           {
                               Users.UsersID,
                               Users.UsersName,
                               Users.UsersPwd,
                               Users.UsersSex,
                               Users.UsersPhone,
                               Users.UsersEmail,
                               Users.PositionID,
                               Users.RoleID,
                               Users.EntryTime,
                               Users.QuitTime,
                               Users.StateID,
                               Users.Remarks,
                               Position.PositionName,
                               Position.ParentID,Position.Grade,
                               Users.Nickname
                           }).FirstOrDefault();
            return UserName;
        }
        public IEnumerable<object> Agency_JieBan()
        {
            var Agencys = (from commission in _context.commissions
                           join Organizations in _context.organizations on commission.OrganizationId equals Organizations.OrganizationID
                           join application in _context.applications on commission.Aid equals application.Aid
                           join state in _context.Process_application_State on commission.Process_State_ID equals state.Process_State_ID
                           join Position in _context.Position on commission.Manager_id equals Position.PositionID
                           join Users in _context.Users on Position.PositionID equals Users.PositionID
                       
                           select new
                           {
                               commission.Agency_ID,
                               commission.Aid,
                               commission.ApplicationName,
                               Organizations.OrganizationName,
                               application.Aname,
                               commission.Manager_id,
                               commission.Director_Id,
                               state.Process_State_Name,
                               commission.CreateTime,
                               state.Process_State_ID,
                               Users.UsersName,
                              Users.Nickname
                           }).ToList();
            return Agencys;
        }

        //若添加申请单成功的同时添加代办表时失败要根据流水号删除申请表
        public int Delete(int Agency_ID) {
            var LeaveFrom = _context.commissions.Where(a => a.Agency_ID.Equals(Agency_ID)).FirstOrDefault();
            _context.commissions.Remove(LeaveFrom);
            return _context.SaveChanges();
        }

        public int Update(string Agency_ID, int Process_State_ID,string AYiJian)
        {
            if (AYiJian==null)
            {
                AYiJian = "";
            }
            //_context.commissions.Where(a=>a.Agency_ID.Equals(Agency_ID))
            var u = _context.commissions.Where(a=>a.Agency_ID== Agency_ID).FirstOrDefault();
            u.Process_State_ID = Process_State_ID;
            u.AYiJian = AYiJian;
            _context.commissions.Update(u);
            return _context.SaveChanges();
        }
        public IEnumerable<object> Agencys() {
            var Agencys = (from commission in _context.commissions
                           join Organizations in _context.organizations on commission.OrganizationId equals Organizations.OrganizationID
                           join application in _context.applications on commission.Aid equals application.Aid
                           join state in _context.Process_application_State on commission.Process_State_ID equals state.Process_State_ID
                           join Position in _context.Position on commission.Manager_id equals Position.PositionID
                           join Users in _context.Users on Position.PositionID equals Users.PositionID
                           // where state.Process_State_ID == State || state.Process_State_ID == States
                           select new
                           {
                               commission.Agency_ID,
                               commission.Aid,
                               commission.ApplicationName,
                               Organizations.OrganizationName,
                               application.Aname,
                               commission.Manager_id,
                               commission.Director_Id,
                               state.Process_State_Name,
                               commission.CreateTime,
                               state.Process_State_ID,
                               Users.UsersName,
                               Users.Nickname
                           }).ToList();
            return Agencys;

        }
        //流程详情查询
        public IEnumerable<object> getComXQ(int Aid, string AgencyID) {
            List<object> list = new List<object>();
            
           
                #region 请假申请LeaveForm

            var list1=  (from commission in _context.commissions
                        join application in _context.applications on commission.Aid equals application.Aid
                        join LeaveForm in _context.LeaveForm on commission.Agency_ID equals LeaveForm.Agency_ID
                        join Organization in _context.organizations
                        on commission.OrganizationId equals Organization.OrganizationID
                        join state in _context.Process_application_State
                         on commission.Process_State_ID equals state.Process_State_ID
                        join Leave_Type in _context.Leave_Type
                           on LeaveForm.Leave_Type_ID equals Leave_Type.Leave_Type_ID
                        where commission.Agency_ID == AgencyID && commission.Aid==Aid
                        select new {
                            commission.Agency_ID,
                            commission.Manager_id,
                            commission.Director_Id,
                            commission.ApplicationName,
                           Organization.OrganizationName,
                           application. Aname,
                           state.Process_State_Name,
                            commission.CreateTime,
                          Leave_Type.Leave_Type_Name,
                            LeaveForm.Sex,
                            LeaveForm.LeaveMatter
                        }).ToList();
            foreach (var item in list1)
            {
                list.Add(item);
            }


            #endregion
            #region Purchase_Goods采购申请
            var list2 = (
                   from commission in _context.commissions
                       join application in _context.applications on commission.Aid equals application.Aid
                       join Purchase_goods in _context.Purchase_Goods on commission.Agency_ID equals Purchase_goods.Agency_ID
                       join Organization in _context.organizations
                       on commission.OrganizationId equals Organization.OrganizationID
                       join state in _context.Process_application_State
                        on commission.Process_State_ID equals state.Process_State_ID                      
                       where commission.Agency_ID == AgencyID && commission.Aid == Aid
                       select new
                       {
                           commission.Agency_ID,
                           commission.ApplicationName,
                           commission.Manager_id,
                           commission.Director_Id,
                           Organization.OrganizationName,
                           application.Aname,
                           state.Process_State_Name,
                           commission.CreateTime,
                            Purchase_goods.Purchase_goods_Name,
                           Purchase_goods.Unit_Price,
                           Purchase_goods.GoodCount,
                           Purchase_goods.Subtotal
                       }).ToList();
            foreach (var item in list2)
            {
                list.Add(item);
            }
            #endregion
            #region [dbo].[demand] --人员需求申请表3

                   var list3= (
                    from commission in _context.commissions
                    join application in _context.applications on commission.Aid equals application.Aid
                    join Demand in _context.demand on commission.Agency_ID equals Demand.Agency_ID
                    join Organization in _context.organizations
                    on commission.OrganizationId equals Organization.OrganizationID
                    join state in _context.Process_application_State
                     on commission.Process_State_ID equals state.Process_State_ID
                
                    where commission.Agency_ID == AgencyID && commission.Aid == Aid
                    select new
                    {
                        commission.Agency_ID,
                        commission.Manager_id,
                        commission.Director_Id,
                        commission.ApplicationName,
                        Organization.OrganizationName,
                        application.Aname,
                        state.Process_State_Name,
                        commission.CreateTime,
                        Demand.Stime,
                        Demand.Rtime,
                        Demand.DeName,
                        Demand.num,
                        Demand.cause,
                        Demand.need
                    }).ToList();
            foreach (var item in list3)
            {
                list.Add(item);
            }
            #endregion
            #region [ordering] --订单申请表4

                var list4=    (
                    from commission in _context.commissions
                    join application in _context.applications on commission.Aid equals application.Aid
                    join Ordering in _context.ordering on commission.Agency_ID equals Ordering.Agency_ID
                    join Organization in _context.organizations
                    on commission.OrganizationId equals Organization.OrganizationID
                    join state in _context.Process_application_State
                     on commission.Process_State_ID equals state.Process_State_ID
                   join OrderState in _context.OrderStates on Ordering.OrderStateID
                   equals OrderState.OrderStateID 
                   join Set_meal in _context.setmeal on Ordering.MealID equals Set_meal.MealID
               
                    where commission.Agency_ID == AgencyID && commission.Aid == Aid
                    select new
                    {
                        commission.Agency_ID,
                        commission.Manager_id,
                        commission.Director_Id,
                        commission.ApplicationName,
                        Organization.OrganizationName,
                        application.Aname,
                        state.Process_State_Name,
                        commission.CreateTime,
                        Ordering.Otime,
                        OrderState.OrderStateName,
                        Set_meal.MealName,
                        Ordering.OrName,
                    }).ToList();
            foreach (var item in list4)
            {
                list.Add(item);
            }
            #endregion
            #region expense_Reimbursements--报销费用申请表5

                  var list5=  (
                    from commission in _context.commissions
                    join application in _context.applications on commission.Aid equals application.Aid
                    join Expense_reimbursement in _context.expense_Reimbursements on commission.Agency_ID equals Expense_reimbursement.Agency_ID
                    join Organization in _context.organizations
                    on commission.OrganizationId equals Organization.OrganizationID
                    join state in _context.Process_application_State
                     on commission.Process_State_ID equals state.Process_State_ID
                    join Position in _context.Position on  Expense_reimbursement.PositionID equals Position.PositionID
                    join Types in _context.expense_Reimbursement_Types on Expense_reimbursement.erId equals Types.erTId
                    join Users in _context.Users on Position.PositionID equals Users.PositionID
                    where commission.Agency_ID == AgencyID && commission.Aid == Aid
                    select new
                    {
                        commission.Agency_ID,
                        commission.ApplicationName,
                        commission.Manager_id,
                        commission.Director_Id,
                        Organization.OrganizationName,
                        application.Aname,
                        state.Process_State_Name,
                        commission.CreateTime,
                        Expense_reimbursement.erName,
                        Expense_reimbursement.erTime,
                       Position.PositionName,
                       Types.erTName,
                        Expense_reimbursement.erFY,
                        Expense_reimbursement.erBZ,
                        Users.UsersName,
                        Users.Nickname
                    }).ToList();
            foreach (var item in list5)
            {
                list.Add(item);
            }
            #endregion
            #region [Travel_On_Businesses]  差旅费用申请表6

               var list6=     (
                    from commission in _context.commissions
                    join application in _context.applications on commission.Aid equals application.Aid
                    join travel in _context.Travel_On_Businesses on commission.Agency_ID equals travel.Agency_ID
                    join Organization in _context.organizations
                    on commission.OrganizationId equals Organization.OrganizationID
                    join state in _context.Process_application_State
                     on commission.Process_State_ID equals state.Process_State_ID
                    join Position in _context.Position on travel.PositionID equals Position.PositionID
                    where commission.Agency_ID == AgencyID && commission.Aid == Aid
                    join Users in _context.Users on Position.PositionID equals Users.PositionID
                    select new
                    {
                        commission.Agency_ID,
                        commission.ApplicationName,
                        commission.Manager_id,
                        commission.Director_Id,
                        Organization.OrganizationName,
                        application.Aname,
                        state.Process_State_Name,
                        commission.CreateTime,
                        travel.tbName,
                        travel.tbTime,
                        travel.tbSY,
                        Position.PositionName,
                        travel.tbBeginAddress,
                        travel.tbEndAddress,
                        travel.tbBeginTime,
                        travel.tbEndTime,
                        travel.tbJTF,
                        travel.tbCYF,
                        travel.tbZSF,
                        travel.tbSum,
                        travel.tbZB,
                        Users.UsersName,Users.Nickname
                    }).ToList();
            foreach (var item in list6)
            {
                list.Add(item);
            }
            #endregion

            return list;

        }

    }
}
